﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace ADOdotNetDemo1
{
    class Program
    {
        static void Main(string[] args)
        {

            int[] arr = new int[3];
            arr[0] = 12;
            arr[1] = 4;
            arr[2] = 11;

            for (int i = 0; i < arr.Length; i++)
            {
                Console.Write(arr[i] + " ");
            }
            Console.WriteLine();
            foreach (int n in arr)
            {
                Console.Write(n + " ");
            }
            Console.Read();

            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet dataSet = new DataSet();


            conn.ConnectionString = "server=localhost; database=BankDB; user id=sa; password=sa123";
            cmd.CommandText = "select * from customer";
            cmd.Connection = conn;

            adapter.SelectCommand = cmd;
            conn.Open();
            adapter.Fill(dataSet, "customer");
            cmd.CommandText = "select * from userx";
            adapter.SelectCommand = cmd;
            adapter.Fill(dataSet, "userx");
            conn.Close();
            DataTable dtcust = dataSet.Tables["customer"];
            for (int i = 0; i < dtcust.Rows.Count;i++ )
            {
               string cid= dtcust.Rows[i]["CustomerID"].ToString();
            }
                foreach (DataRow row in dtcust.Rows)
                {
                    long cid = Convert.ToInt64(row["CustomerID"]);
                    string cnmae = row.ItemArray[1].ToString();
                    double bamt = Convert.ToDouble(row["BalanceAmt"]);
                    string nacno = row.ItemArray[3].ToString();
                    Console.WriteLine(cid + " " + cnmae + " " + bamt + " " + nacno);
                }
            Console.Read();



            DataRow custRow = dataSet.Tables["customer"].NewRow();
            custRow["CustomerName"] = "Rudra";
            custRow["BalanceAmt"] = 123556.56;
            custRow["BankAcNo"] = "S-123";

            dataSet.Tables["customer"].Rows.Add(custRow);

            cmd.CommandText = "insert into Customer(CustomerName,BalanceAmt,BankAcNo) values(@CustomerName,@BalanceAmt,@BankAcNo)";
            cmd.Parameters.Add("@CustomerName", SqlDbType.Text, 150, "CustomerName");
            cmd.Parameters.Add("@BalanceAmt", SqlDbType.Decimal, 18, "BalanceAmt");
            cmd.Parameters.Add("@BankAcNo", SqlDbType.Text, 250, "BankAcNo");
            
            adapter.InsertCommand = cmd;
            
            conn.Open();
            adapter.Update(dataSet, "customer"); //update to database
            conn.Close();
        }
    }
}
